package com.scott.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.scott.user.form.User;
import com.scott.user.utils.DBUtil;

public class UserDao {
	
	
	
	/**
	 * 用户新增
	 * @param user
	 */
	public void addUser(User user){
		String sql= "insert into users(username,password) values(?,?)";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps=null;
		try {
			ps = connection.prepareStatement(sql);
			
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			
			//执行sql
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closePreparedStatement(ps);
			DBUtil.closeConnection(connection);
		}
	}
	
	
	
	
	/**
	 * 根据id删除用户
	 * @param user
	 */
	public void deleteUser(User user){
		String sql= "delete from users where id=?";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps=null;
		try {
			ps = connection.prepareStatement(sql);
			
			ps.setInt(1, user.getId());
			
			//执行sql
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closePreparedStatement(ps);
			DBUtil.closeConnection(connection);
		}
	}
	
	
	public void updateUser(User user){
		String sql= "update users set username=? , password=? where id=?";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps=null;
		try {
			ps = connection.prepareStatement(sql);
			
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setInt(3, user.getId());
			
			//执行sql
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closePreparedStatement(ps);
			DBUtil.closeConnection(connection);
		}
	}
	
	
	/**
	 * 查询所有的用户
	 * @return
	 */
	public List<User> getUsers(){
		
		List<User> list = new ArrayList<User>();
		
		String sql ="select * from users order by id desc";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps = connection.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				
				User user= new User();
				
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeQueryResource(rs, ps, connection);
		}
		
		return list;
		
	}
	
	
	/**
	 * 根据用户id查询到用户对象
	 * @return
	 */
	public User getUserById(Integer id){
		
		String sql ="select * from users where id = ?";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		User user= new User();
		try {
			ps = connection.prepareStatement(sql);
			
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeQueryResource(rs, ps, connection);
		}
		
		return user;
		
	}
	
	/**
	 * 根据用户名查询用户是否存在
	 * @return
	 */
	public boolean checkUserIsExist(User user){
		String sql ="select * from users where username=?";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		boolean flag= false;
		try {
			ps = connection.prepareStatement(sql);
			
			//在查询之前需要先把用户名设置好
			ps.setString(1, user.getUsername());
			
			rs = ps.executeQuery();
			while(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeQueryResource(rs, ps, connection);
		}
		
		return flag;
		
	}

}
